# -*- coding: utf-8 -*-
"""
Created on Wed Jun  6 10:24:36 2018

@author: xuchao
"""

# -*- coding: utf-8 -*-
"""
Created on Fri Jun  1 16:05:44 2018

@author: xuchao
"""
#%%

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
plt.rcParams['axes.unicode_minus']=False #用来正常显示负号
import datetime
from datetime import datetime
import pymysql
import os
os.chdir('./')
now = datetime.strftime(datetime.now(),'%Y-%m-%d')
filename='账龄损失率兴业尽调%snew.xlsx'%now
if os.path.exists(filename):
    os.remove(filename)

writer = pd.ExcelWriter(filename)

sql1 = '''
SELECT
	user_name '客户姓名',
	contract_id,
	loan_amount / 100 '放款金额',
	loan_term '分期期数',
	YEAR (loan_at) 'YEAR',
	QUARTER (loan_at) 'QUARTER',
	MONTH (loan_at) 'MONTH',
	WEEK (loan_at) 'WEEK',
	DATE_FORMAT(loan_at, '%Y-%m-%d') '放款时间',
	DATE_FORMAT(loan_at, '%Y-%m') '放款月份',
	term '期数',
	DATE_FORMAT(repay_date, '%Y-%m-%d') '计划还款时间',
	DATE_FORMAT(repay_date, '%Y-%m') '计划还款月份',
	pay_ben_jin / 100 '应还本金',
	pay_loan_interest / 100 '应还利息',
	pay_platform_interest / 100 '应还平台服务费',
	counted_should_pay / 100 '应还总额',
	pay_platform_service / 100 '应还放款手续费',
	total_pay / 100 '总计应还',
	remain_ben_jin / 100 '剩余本金',
	STATUS '还款状态',
	overdue_status,
	DATE_FORMAT(end_date, '%Y-%m-%d') '实际还款时间',
	DATE_FORMAT(end_date, '%Y-%m') '实还月份',
	already_pay_ben_jin / 100 '实还本金',
	already_pay_loan_interest / 100 '实还利息',
	already_pay_fund_side_service / 100 '还款管理费',
	already_pay_platform_interest / 100 '平台服务费',
	already_pay_overdue_amount / 100 '实还罚息',
	early_settled_handle_charge / 100 '实还提前还款手续费'
FROM
	qy_repayment_prod.repay_plans
ORDER BY
	loan_at,
	contract_id,
	term;

'''


try:
    conn = pymysql.connect(host='172.16.1.90', port=3306,
                           user='lianghua', passwd='xc123',
                           charset='UTF8')
    with conn.cursor() as cur:
        cur.execute(sql1)
        
        df1 = pd.read_sql(sql1,conn)
        
finally:
    cur.close()
    conn.close()

a1 = {}
a2 = {}
a3 = {}

for i in df1.index:
    a1[i] = str(df1['YEAR'].iloc[i])+'-Q-'+str(df1['QUARTER'].iloc[i])
    a2[i] = str(df1['YEAR'].iloc[i])+'-M-'+str(df1['MONTH'].iloc[i])
    a3[i] = str(df1['YEAR'].iloc[i])+'-W-'+str(df1['WEEK'].iloc[i])
df1['统计季度'] = pd.Series(a1)
df1['统计月份'] = pd.Series(a2)
df1['统计周'] = pd.Series(a3)

# =============================================================================
#:按照月度进行计算
#df_17M6 = df1.loc[df1['放款月份']<='2017-06']
#df_17M7 = df1.loc[(df1['放款月份']=='2017-07')]
#df_17M9 = df1.loc[(df1['放款月份']=='2017-09')]
#df_17M10 = df1.loc[(df1['放款月份']=='2017-10')]
#df_17M11 = df1.loc[(df1['放款月份']=='2017-11')]
#df_17M12 = df1.loc[(df1['放款月份']=='2017-12')]
#df_18M01 = df1.loc[(df1['放款月份']=='2018-01')]
#df_18M02 = df1.loc[(df1['放款月份']=='2018-02')]
#df_18M03 = df1.loc[(df1['放款月份']=='2018-03')]
#df_18M04 = df1.loc[(df1['放款月份']=='2018-04')]
#
#df_QL = [df_17M6,df_17M7,df_17M9,df_17M10,df_17M11,df_17M12,df_18M01,df_18M02,df_18M03,df_18M04]
#k = ['17M6','17M7','17M9','17M10','17M11','17M12','18M01','18M02','18M03','18M04']
#k1=['累计逾期比例M1+(贷款余额)','累计逾期比例M1+(放款金额)','累计逾期比例M1+(人数)']
# =============================================================================






def calculate(df_17Q3):
    xlist=[]#记录每期期末所有的信息
    ylist=[]#记录金额每期期末逾期率（分子为当前逾期客户所有剩余未还本金，分母为当前期末贷款余额）
    zlist=[]#记录金额每期期末逾期率（分子为当前逾期客户所有剩余未还本金，分母为当下所有客户放款金额）
    plist=[]#记录人数的每期期末逾期率（分子为当前逾期客户人数，分母为当前所有客户）
    df_17Q3['实际还款时间'].fillna('2099-09-09',inplace=True)####填充空白地方，以便进行时间的比较
    if 'unpay' in list(df_17Q3['还款状态']):
        trem_list = [i+1 for i in range(df_17Q3['期数'].loc[df_17Q3['还款状态']=='unpay'].min()-1)]
    else:
        trem_list = list(set(list(df_17Q3['期数'])))
    
    for i in trem_list:
    
        df_17Q3_1 = df_17Q3.loc[df_17Q3['期数']<=i]
        df_17Q3_1_01 = df_17Q3_1[['contract_id','计划还款时间']].groupby('contract_id').apply(lambda s : s[s.计划还款时间==s.计划还款时间.max()]).reset_index(drop=True)
        df_17Q3_1_01.rename(columns={'计划还款时间':'query_date'},inplace=True)
        df_17Q3_1 = pd.merge(df_17Q3_1,df_17Q3_1_01,left_on='contract_id',right_on='contract_id',how='left')
        f = {}#用来重新记录当时的状态
        for i in df_17Q3_1.index:
            if df_17Q3_1['计划还款时间'].iloc[i]>df_17Q3_1['实际还款时间'].iloc[i]:
                f[i] = 'paid'#'early_settled'
            elif df_17Q3_1['计划还款时间'].iloc[i]==df_17Q3_1['实际还款时间'].iloc[i]:
                f[i] = 'paid'
            else:
            # df_17Q3_1['计划还款时间']<df_17Q3_1['实际还款时间']:
                if df_17Q3_1['实际还款时间'].iloc[i]=='2099-09-09':
                    f[i] = 'overdue'
                else:
                    #取最大的期数的那一期的应还款时间作为对比
                   if df_17Q3_1['query_date'].iloc[i]<df_17Q3_1['实际还款时间'].iloc[i]:
                       f[i] = 'overdue'
                   else:
                       f[i] = 'paid'
        
        df_17Q3_1['flag'] =pd.Series(f)      
        
        #print(df_17Q3_1)
        
        d17Q3_1 = {}#用来记录当时所有状态下的金额和人数信息
        d17Q3_1['放款金额'] = df_17Q3[['放款金额','contract_id']].drop_duplicates('contract_id').放款金额.sum()
            
        overdue_list = list(set(df_17Q3_1['contract_id'].loc[(df_17Q3_1['flag']=='overdue')]))#拿出逾期客户ID
        
        d17Q3_1['逾期客户人数']=len(overdue_list)
        d17Q3_1['当前所有客户人数']=len(df_17Q3_1_01)
        
        if len(overdue_list)==0:
            d17Q3_1['逾期客户放款金额']=0
            d17Q3_1['逾期客户已还本金']=0
            d17Q3_1['逾期客户当前逾期本金']=0
            d17Q3_1['逾期客户剩余未还本金']=0
            d17Q3_1['逾期本金']=0
            d17Q3_1['兴业逾期本金'] = 0#*********************************************************************************************兴业银行指标

            
        else:
            blist=[]#逾期客户放款金额
            clist=[]#逾期客户已还本金
            dlist=[]#逾期客户当前逾期的本金****************************************************************************************************1
            elist=[]#逾期本金(M1-M3算当前逾期的所有本金，M3+算所有的剩余未还本金)*******************************************************************2
            glist=[]#***************************************兴业指标，逾期30天以上的剩余未还本金*************************************************4

            for j in overdue_list:
                blist.append(df_17Q3_1[['放款金额','contract_id']].loc[df_17Q3_1['contract_id']==j].drop_duplicates('contract_id').放款金额.sum())
                #clist.append(df_17Q3_1['应还本金'].loc[(df_17Q3_1['contract_id']==j)&(df_17Q3_1['flag']=='paid')].sum())
                clist.append(df_17Q3_1['实还本金'].loc[(df_17Q3_1['contract_id']==j)&(df_17Q3_1['flag']=='paid')].sum())
                dlist.append(df_17Q3_1['应还本金'].loc[(df_17Q3_1['contract_id']==j)&(df_17Q3_1['flag']=='overdue')].sum())
                if len(df_17Q3_1['应还本金'].loc[(df_17Q3_1['contract_id']==j)&(df_17Q3_1['flag']=='overdue')])<=3:
                    elist.append(df_17Q3_1['应还本金'].loc[(df_17Q3_1['contract_id']==j)&(df_17Q3_1['flag']=='overdue')].sum())
                else:
                    #elist.append((df_17Q3_1[['放款金额','contract_id']].loc[df_17Q3_1['contract_id']==j].drop_duplicates('contract_id').放款金额.sum())-(df_17Q3_1['应还本金'].loc[(df_17Q3_1['contract_id']==j)&(df_17Q3_1['flag']=='paid')].sum()))
                    elist.append((df_17Q3_1[['放款金额','contract_id']].loc[df_17Q3_1['contract_id']==j].drop_duplicates('contract_id').放款金额.sum())-(df_17Q3_1['实还本金'].loc[(df_17Q3_1['contract_id']==j)&(df_17Q3_1['flag']=='paid')].sum()))

                if len(df_17Q3_1['应还本金'].loc[(df_17Q3_1['contract_id']==j)&(df_17Q3_1['flag']=='overdue')])>=2:
                    #glist.append((df_17Q3_1[['放款金额','contract_id']].loc[df_17Q3_1['contract_id']==j].drop_duplicates('contract_id').放款金额.sum())-(df_17Q3_1['应还本金'].loc[(df_17Q3_1['contract_id']==j)&(df_17Q3_1['flag']=='paid')].sum()))
                    glist.append((df_17Q3_1[['放款金额','contract_id']].loc[df_17Q3_1['contract_id']==j].drop_duplicates('contract_id').放款金额.sum())-(df_17Q3_1['实还本金'].loc[(df_17Q3_1['contract_id']==j)&(df_17Q3_1['flag']=='paid')].sum()))

                d17Q3_1['逾期客户放款金额'] = sum(blist)
                d17Q3_1['逾期客户已还本金'] = sum(clist)
                d17Q3_1['逾期客户当前逾期本金'] = sum(dlist)
                d17Q3_1['逾期本金'] = sum(elist)#逾期本金(M1-M3算当前逾期的所有本金，M3+算所有的剩余未还本金)******************************************2
                d17Q3_1['逾期客户剩余未还本金'] = d17Q3_1['逾期客户放款金额']-d17Q3_1['逾期客户已还本金']#********************************************3
                d17Q3_1['兴业逾期本金'] = sum(glist)#******************************兴业指标，逾期30天以上的剩余未还本金******************************4
                
        
        
        d17Q3_1['已还本金'] = df_17Q3_1['应还本金'].loc[(df_17Q3_1['flag']=='paid')].sum()
        d17Q3_1['贷款余额'] = d17Q3_1['放款金额']-d17Q3_1['已还本金']
        d17Q3_1[k1[0]] = d17Q3_1['逾期客户剩余未还本金']/d17Q3_1['贷款余额']#*************************************************分子视情况更改为1,2,3，4
        d17Q3_1[k1[1]] = d17Q3_1['逾期客户剩余未还本金']/d17Q3_1['放款金额']#**************************************************分子视情况更改为1,2,3,4
        d17Q3_1[k1[2]] = d17Q3_1['逾期客户人数']/d17Q3_1['当前所有客户人数']
        xlist.append(d17Q3_1)
        ylist.append(d17Q3_1[k1[0]])
        zlist.append(d17Q3_1[k1[1]])
        plist.append(d17Q3_1[k1[2]])
        
        
    df_l = []      
    for i in range(len(xlist)):
        df_l.append(pd.DataFrame(xlist[i],index=[i+1]))    
    df_xlist=pd.concat(df_l)    
    
    
    return df_xlist,ylist,zlist,plist

# =============================================================================

df_QL = []
item = ['统计季度','统计月份','统计周'][0]
k = list(df1[item].value_counts().index)
for quarter in k:
    df_quarter = df1.loc[df1[item]==quarter]
    df_QL.append(df_quarter)
        
    
# 按照季度进行计算
#df_17Q3 = df1.loc[df1['放款月份']<='2017-09']
#df_17Q4 = df1.loc[(df1['放款月份']>'2017-09')&(df1['放款月份']<='2017-12')]
#df_18Q1 = df1.loc[(df1['放款月份']>'2017-12')&(df1['放款月份']<='2018-03')]
#df_18Q2 = df1.loc[(df1['放款月份']>'2018-03')&(df1['放款月份']<='2018-06')]
#df_QL = [df_17Q3,df_17Q4,df_18Q1,df_18Q2]
#k = ['17Q3','17Q4','18Q1','18Q2']
k1=['累计逾期比例M1+(贷款余额)','累计逾期比例M1+(放款金额)','累计逾期比例M1+(人数)']
# =============================================================================

for j in list(range(len(k1))):
    p=0
    alist=[]
    blist=[]
    for i in df_QL: 
        df_Q = calculate(i)[0]
        df_Q['统计周期'] = k[p]
        df_Q.reset_index(inplace=True)
        #df_Q.to_excel(writer,sheet_name='%s'%(k[p])) 
        blist.append(df_Q)
        df = pd.DataFrame(calculate(i)[j+1],columns=[k[p]])
        alist.append(df)
        p+=1

    df_Q_all = pd.concat(blist)
    df_Q_all['year'] = df_Q_all['统计周期'].map(lambda s : int(s[:4]))
    df_Q_all['item'] = df_Q_all['统计周期'].map(lambda s : int(s[7:]))
    df_Q_all = df_Q_all.sort_values(['year','item']).drop(['year','item'],axis=1).reset_index(drop=True)    


    df_Q_all.to_excel(writer,sheet_name='统计数据')
    df_finally = pd.concat(alist,axis=1)

    df_finally['flag'] = [i+1 for i in range(len(df_finally))]
    df_fin = df_finally.set_index('flag').T.reset_index()
    df_fin['year'] = df_fin['index'].map(lambda s : int(s[:4]))
    df_fin['item'] = df_fin['index'].map(lambda s : int(s[7:]))
    df_fin = df_fin.sort_values(['year','item']).drop(['year','item'],axis=1).set_index('index')      
    df_fin.to_excel(writer,sheet_name='账龄损失率__%s'%(k1[j]))
     
    for i in range(len(df_fin)):
        plt.plot(list(df_fin.columns),list(df_fin.iloc[i]),label=df_fin.index[i])
    plt.xlabel('期数')
    plt.ylabel('%s'%(k1[j]))
    plt.title('账龄损失率')
    plt.legend(loc='upper left')
    plt.show()
writer.save()

from sqlalchemy import create_engine
data_info = {
        'user':'root',
        'password':'root',
        'host':'localhost',
        'database':'repayment'
        }
engine = create_engine('mysql+pymysql://%(user)s:%(password)s@%(host)s/%(database)s?charset=utf8' % data_info,encoding='utf-8')
pd.io.sql.to_sql(df_Q_all,'vintage',con = engine,if_exists='replace')

